package edu.gatech.arktos;

import java.io.IOException;
import java.net.URL;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import edu.gatech.arktos.Team;
import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.util.ServiceException;

public class GradesDB {
        private SpreadsheetEntry entry;
        private SpreadsheetService service;
        private LinkedHashSet<Student> students = new LinkedHashSet<Student>();

        /**
         * Constructor
         */
        public GradesDB(SpreadsheetEntry entry) {
                this.entry = entry;
                this.service = (SpreadsheetService) entry.getService();
                this.students = getStudents();
        }

        /**
         * Gets the number of students
         * 
         * @return numStudents
         */
        public int getNumStudents() { // Cassidy
                int numStudents = 0;
                try {
                        URL cellFeedUrl = entry.getWorksheets().get(0).getCellFeedUrl();
                        CellQuery query = new CellQuery(cellFeedUrl);
                        query.setMinimumRow(2);
                        query.setMinimumCol(1);
                        query.setMaximumCol(1);
                        CellFeed feed = service.query(query, CellFeed.class);
                        for (CellEntry entry : feed.getEntries()) {
                                numStudents++;
                        }
                } catch (Exception e) {

                }

                return numStudents;
        }

        /**
         * Gets the number of assignments
         * 
         * @return numAssign
         */
        public int getNumAssignments() { // Cassidy
                int numAssign = 0;
                try {
                        URL cellFeedUrl = entry.getWorksheets().get(1).getCellFeedUrl();
                        CellQuery query = new CellQuery(cellFeedUrl);
                        query.setMinimumRow(2);
                        query.setMinimumCol(4);
                        query.setMaximumCol(4);
                        CellFeed feed = service.query(query, CellFeed.class);
                        for (CellEntry entry : feed.getEntries()) {
                                numAssign++;
                        }
                } catch (Exception e) {

                }

                return numAssign;
        }

        /**
         * Get the number of Projects
         * 
         * @return numProj
         */
        public int getNumProjects() { // Taylor
                int numProj = 0;
                try {
                        URL cellFeedUrl = entry.getWorksheets().get(1).getCellFeedUrl();
                        CellQuery query = new CellQuery(cellFeedUrl);
                        query.setMinimumRow(2);
                        query.setMinimumCol(1);
                        query.setMaximumCol(1);
                        CellFeed feed = service.query(query, CellFeed.class);
                        for (CellEntry entry : feed.getEntries()) {
                                numProj++;
                        }
                } catch (Exception e) {

                }

                return numProj;
        }

        /**
         * Get all the students and returns a hashset of Students
         * -Gets all assignments as well as the grades/averages for that student (Daranhdara Hun)
         * @return student - HashSet<Students>
         */
        public LinkedHashSet getStudents() { // Taylor
                
                //students = new HashSet<Student>();
                students = new LinkedHashSet();
                int n = getNumStudents();
                String studentName;
                String studentID;
                String attendance;
                String email;
                List<Assignment> assignments = new ArrayList<Assignment>();
                int avgGrade,grade1,grade2,grade3;

                URL cellFeedUrl1 = null;
                URL cellFeedUrl2 = null;
                URL cellFeedUrl_Grades = null;
                
                try {
                        cellFeedUrl1 = entry.getWorksheets().get(0).getCellFeedUrl(); //0 is "details" tab on spreadsheet
                        cellFeedUrl2 = entry.getWorksheets().get(2).getCellFeedUrl(); //2 is "Attendence" tab
                        cellFeedUrl_Grades = entry.getWorksheets().get(3).getCellFeedUrl();
                } catch (Exception e) {

                }
                CellQuery query1 = new CellQuery(cellFeedUrl1);
                CellQuery query2 = new CellQuery(cellFeedUrl2); 
                CellQuery query_Grades = new CellQuery(cellFeedUrl_Grades);
        
                query1.setMinimumCol(1);
                query1.setMaximumCol(3);
                query2.setMinimumCol(2);
                query2.setMaximumCol(2);
                query_Grades.setMinimumCol(2);
                query_Grades.setMaximumCol(5);
                
                CellFeed feed = null;
                CellFeed feed2 = null;
                CellFeed feed_Grades = null;
                
                for (int i = 1; i <= n; i++) {

                        try {

                                /* Specify range of students  */
                                query1.setMinimumRow(i + 1);
                                query1.setMaximumRow(i + 1);
                                /* Specify range of attendance  */
                                query2.setMinimumRow(i + 2);
                                query2.setMaximumRow(i + 2);
                                /* Specify range of grades  */
                                query_Grades.setMinimumRow(i + 1);
                                query_Grades.setMaximumRow(i + 1);      
                                
                                feed = service.query(query1, CellFeed.class);
                                studentName = feed.getEntries().get(0).getPlainTextContent();
                                studentID = feed.getEntries().get(1).getPlainTextContent();
                                email = feed.getEntries().get(2).getPlainTextContent();
                                
                                
                                feed2 = service.query(query2, CellFeed.class);
                                attendance = feed2.getEntries().get(0).getPlainTextContent();
                                
                                feed_Grades = service.query(query_Grades, CellFeed.class);
                                avgGrade = 50;//Integer.parseInt(feed_Grades.getEntries().get(0).getPlainTextContent());
                                
                                /* Get grades per student from Grades Tab in spreadsheet */
                                int[] grades = new int[3];
                                String grades1 = feed_Grades.getEntries().get(1).getPlainTextContent();
                                String grades2 = feed_Grades.getEntries().get(2).getPlainTextContent();
                                String grades3 = feed_Grades.getEntries().get(3).getPlainTextContent();
                        
                                grades[0] = Integer.parseInt(grades1); //Assignment 1
                                grades[1] = Integer.parseInt(grades2); //Assignment 2
                                grades[2] = Integer.parseInt(grades3); //Assignment 3
                                assignments = getListOfAssignments(grades);
                                
                                attendance = attendance.substring(0, attendance.length() - 1);
                                students.add(new Student(studentID, studentName, attendance, assignments, avgGrade, email));
                                
                        } catch (Exception e) {
                                e.printStackTrace();
                        }
                }
                this.students = students;
                return students;
        }

        /**
         * 
         * @param grades
         * @author Daranhdara Hun
         */
        @SuppressWarnings("null")
        private List<Assignment> getListOfAssignments(int[] grades){
                List<Assignment> assList= new ArrayList<Assignment>();
                int size = grades.length, i;
                for (i=0;i<size;i++){
                        Assignment a;
                        a = getAssignmentByNumber(i);
                        a.setGrade(grades[i]);
                        assList.add(a);
                }
                
                return assList;
        }
        
        /**
         * Returns a student object specified by the name
         * 
         * @param studentName
         * @return
         */
        public Student getStudentByName(String studentName) { // Simba
                
                Student temp = null;
                for (Student s : students) {
                        if (s.getName().compareTo(studentName) == 0) {
                                return s;
                        }
                }
                
                return temp;
        }

        /**
         * Gets the student object given the specified id String
         * 
         * @param id
         * @return
         */
        public Student getStudentByID(String id) { // Simba

                Student temp = null;

                for (Student s : students) {
                        if (s.getGtid().compareTo(id) == 0) {
                                return s;
                        }
                }
                return temp;
        }
        
        /**
         * Gets data from  Data Tab
         * @author Daranhdara Hun
         */
        public Assignment getAssignmentByNumber(int num){
                Assignment assignment = null;
                String assignName="", assignDesc="";
                URL cellFeedUrl;
                try {
                        /* Build the URL */
                        cellFeedUrl = entry.getWorksheets().get(1).getCellFeedUrl();
                        CellQuery query = new CellQuery(cellFeedUrl);
                        /* Narrow down the group of cells by specifying the rows and cols to bring back */
                        query.setMinimumRow(num+1); //Starts at row 2 originally on the spreadsheet
                        query.setMinimumCol(4);
                        query.setMaximumCol(5);
                        /* Establishes the connection feed to the spreadsheet */
                        CellFeed feed = service.query(query, CellFeed.class);
                        assignName = feed.getEntries().get(0).getCell().getValue().toString();
                        assignDesc = feed.getEntries().get(1).getCell().getValue().toString();


                } catch (IOException e) {
                        e.printStackTrace();
                } catch (ServiceException e) {
                        e.printStackTrace();
                }
                
                
                assignment = new Assignment(assignName, assignDesc);
                
                return assignment;
        }

        @SuppressWarnings("null")
		public Project getProjectByName(String string) {
                Project project = null;
                String projectName="", projectDesc="";
                int average = 0;
                LinkedHashSet<Team> teams= new LinkedHashSet<Team>();
//                Hashtable studContribs = new Hashtable();
                Map<Student, Float> studContribs = new HashMap<Student, Float>();
                
                URL cellFeedUrl;
                URL cellFeedUrl2;
                URL cellFeedUrl_contribs;
                CellQuery query2, query_contribs;
                int num = 0;
                if(string.compareTo("P1") == 0) num = 2;
                if(string.compareTo("P2") == 0) num = 3;
                if(string.compareTo("P3") == 0) num = 4;
                try {
                        /* Build the URL */
                        cellFeedUrl = entry.getWorksheets().get(1).getCellFeedUrl();
                        CellQuery query = new CellQuery(cellFeedUrl);
                        /* Narrow down the group of cells by specifying the rows and cols to bring back */
                        query.setMinimumRow(num); //Starts at row 2 originally on the spreadsheet
                        query.setMinimumCol(1);
                        query.setMaximumCol(2);
                        /* Establishes the connection feed to the spreadsheet */
                        CellFeed feed = service.query(query, CellFeed.class);
                        projectName = feed.getEntries().get(0).getCell().getValue().toString();
                        projectDesc = feed.getEntries().get(1).getCell().getValue().toString();
                        CellFeed feed2;
                        
                        cellFeedUrl = entry.getWorksheets().get(num+5).getCellFeedUrl();
                        query = new CellQuery(cellFeedUrl);                     
                        query.setMinimumRow(10);
                        query.setMaximumRow(10);
                        query.setMinimumCol(3);
                        query.setMaximumCol(5);         
                        feed = service.query(query, CellFeed.class);
                        average += Integer.parseInt(feed.getEntries().get(0).getCell().getValue().toString());
                        average += Integer.parseInt(feed.getEntries().get(1).getCell().getValue().toString());
                        average += Integer.parseInt(feed.getEntries().get(2).getCell().getValue().toString());
                        average /= 3;
                        String teamscore = "0";
                        
                        cellFeedUrl = entry.getWorksheets().get(num+2).getCellFeedUrl();
                        cellFeedUrl_contribs = entry.getWorksheets().get(num+8).getCellFeedUrl(); /* Contribs */
                        query = new CellQuery(cellFeedUrl); 
                        int current_contrib_row = 3;
                        /* Get teams for that project */
                        for(int i = 0; i < 3; i++)
                        {       
                                LinkedHashSet<Student> studentsy = new LinkedHashSet<Student>();
                                                
                                query.setMinimumRow(i+2);
                                query.setMaximumRow(i+2);
                                query.setMinimumCol(2);
                                query.setMaximumCol(6);         
                                feed = service.query(query, CellFeed.class);
                                int teamSize = feed.getEntries().size();
                                
                                /* Create a map of students and rating to team */
                                query_contribs = new CellQuery(cellFeedUrl_contribs);              
                                query_contribs.setMinimumCol(3);
                                query_contribs.setMaximumCol(4);
                                query_contribs.setMinimumRow(current_contrib_row);
                                current_contrib_row = current_contrib_row + teamSize + 1; /* update starting MinimumRow */
                                query_contribs.setMaximumRow(18);
                                CellFeed feed_contribs = service.query(query_contribs, CellFeed.class);
                                
                                /* Gets the students for the team and contributions/ratings */
                                String studName;
                                float studContrib;
                                for(int j =0; j < teamSize ; j++){                                      
                                        //System.out.print(feed.getEntries().get(j).getCell().getValue().toString() + ", ");
                                		studName = feed.getEntries().get(j).getCell().getValue().toString();
                                		studContrib = new Float(feed_contribs.getEntries().get(j).getCell().getValue().toString());
                                		Student stud = getStudentByName(studName);
                                        studentsy.add(stud);
                                        studContribs.put(stud, studContrib);
                                }
                                cellFeedUrl2 = entry.getWorksheets().get(num+5).getCellFeedUrl();
                                query2 = new CellQuery(cellFeedUrl2); /* Grades */
                                query2.setMinimumRow(10);
                                query2.setMaximumRow(17);
                                query2.setMinimumCol(i+3);
                                query2.setMaximumCol(i+3);      
                                feed2 = service.query(query2, CellFeed.class);
                                teamscore = feed2.getEntries().get(feed2.getEntries().size()-1).getCell().getValue().toString();
                               
                                
                                /* Add team to project */
                                Team t = new Team(studentsy, Integer.parseInt(teamscore),studentsy.size() , "Team " + Integer.toString(i+1));
                                t.setRatings(studContribs);
                                teams.add(t);
                                studContribs = new HashMap<Student, Float>();
                                
                        }
                        //System.out.println("\nProject done\n");
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (ServiceException e) {
                        e.printStackTrace();
                }
                
                
                project = new Project(projectName, projectDesc,average , teams);                
                return project;
        }

        public Project getProject(String string) {
                return getProjectByName(string);
        }
        
        /**
         * 
         */
        

}